﻿using System;

using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Odbc;
using MySql.Data.MySqlClient;
using MySql.Data;
using System.Threading;
namespace MetaSearcher
{
    public class RequestPattern
    {
        public readonly int id_request;
        public readonly String resource;
        public readonly int status;
        public readonly DateTime day_start;
        public readonly DateTime day_finish;
        public readonly int frequency;
        public readonly String city_from;
        public readonly String city_to;
        public readonly DateTime current_date;
        public readonly String statistic;
        public RequestPattern(int id_request, String resource, int status, DateTime day_start, DateTime day_finish, int frequency, String city_from, String city_to, String statistic, DateTime current_date)
        {
            this.id_request = id_request;
            this.resource = resource;
            this.day_start = day_start;
            this.day_finish = day_finish;
            this.frequency = frequency;
            this.city_from = city_from;
            this.city_to = city_to;
            this.statistic = statistic;
            this.current_date = current_date;
        }
        public String toString()
        {
            return "" + id_request + "; " + resource + "; " + status + "; " + day_start + "; " + day_finish + "; " + frequency + "; " + city_from + "; " + city_to + "; " + statistic + "; " + current_date + ".";
        }
    }
    public class QueryPattern
    {
        public readonly int id_query;
        public readonly String resource;
        public readonly DateTime date_browse;
        public readonly DateTime date_query;
        public readonly String city_from;
        public readonly String city_to;
        public QueryPattern(int id_query, DateTime date_browse, DateTime date_query, String city_from, String city_to, String resource)
        {
            this.id_query = id_query;
            this.resource = resource;
            this.date_browse = date_browse;
            this.date_query = date_query;
            this.city_from = city_from;
            this.city_to = city_to;

        }
        public String toString()
        {
            return "" + id_query + "; " + date_browse + "; " + date_query + "; " + city_from + "; " + city_to + "; " + resource + ".";
        }
    }
    
    
    
    
    class DBManipulation
    {
        int[] days_per_month = { 1, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 };
        /*
         * Заполнение таблицы City городами и ятами
         */
        public void insertCities(String Connect)
        {
            int[] id_city = new int[100];
            string[] city = new string[100];
            string[] IATA = new string[100];
            string[] country_code = new string[100];
            string[] InsertQuery = new string[100];
            int count = 19;
            id_city[0] = 1; city[0] = "Moscow"; IATA[0] = "MOW"; country_code[0] = "RUS";
            id_city[1] = 2; city[1] = "Berlin"; IATA[1] = "BER"; country_code[1] = "GER";
            id_city[2] = 3; city[2] = "Paris"; IATA[2] = "PAR"; country_code[2] = "FRA";
            id_city[3] = 4; city[3] = "New York"; IATA[3] = "JRA"; country_code[3] = "USA";
            id_city[4] = 5; city[4] = "Prague"; IATA[4] = "PRG"; country_code[4] = "CZE";
            id_city[5] = 6; city[5] = "Vienna"; IATA[5] = "VIE"; country_code[5] = "AUT";
            id_city[6] = 7; city[6] = "Canberra"; IATA[6] = "CBE"; country_code[6] = "AUS";
            id_city[7] = 8; city[7] = "Dubai"; IATA[7] = "DXB"; country_code[7] = "ARE";
            id_city[8] = 9; city[8] = "Amsterdam"; IATA[8] = "AMS"; country_code[8] = "NLD";
            id_city[9] = 10; city[9] = "Saint Petersburg"; IATA[9] = "LED"; country_code[9] = "RUS";
            id_city[10] = 11; city[10] = "London"; IATA[10] = "LCY"; country_code[10] = "GBR";
            id_city[11] = 12; city[11] = "Rome"; IATA[11] = "ROM"; country_code[11] = "ITA";
            id_city[12] = 13; city[12] = "Barcelona"; IATA[12] = "BCN"; country_code[12] = "ESP";
            id_city[13] = 14; city[13] = "Warsaw"; IATA[13] = "WAW"; country_code[13] = "POL";
            id_city[14] = 15; city[14] = "Cairo"; IATA[14] = "CAI"; country_code[14] = "EGI";
            id_city[15] = 16; city[15] = "Bangkok"; IATA[15] = "BKK"; country_code[15] = "THA";
            id_city[16] = 17; city[16] = "Anapa"; IATA[16] = "AAQ"; country_code[16] = "RUS";
            id_city[17] = 18; city[17] = "Washington, DC"; IATA[17] = "DCA"; country_code[17] = "USA";
            id_city[18] = 19; city[18] = "Venice"; IATA[18] = "VCE"; country_code[18] = "ITA";
            id_city[19] = 20; city[19] = "Mumbai"; IATA[19] = "BOM"; country_code[19] = "IND";

            for (int i = 0; i < 20; i++)
            {
                InsertQuery[i] = "insert into city (id_city, city, IATA, country_code) values(" + id_city[i] + ", \"" + city[i] + "\", \"" + IATA[i] + "\", \"" + country_code[i] + "\");";
            }
            //string Connect = "Database=meta;Data Source=188.35.9.179;User Id=Oleg;Password=nanah;";
            MySqlConnection myConnection = new MySqlConnection(Connect);
            try
            {
                myConnection.Open();
                for (int i = 0; i < 20; i++)
                {
                    MySqlCommand myInsert = new MySqlCommand(InsertQuery[i], myConnection);
                    myInsert.ExecuteNonQuery();
                    Console.WriteLine("Insert " + i + " completed");
                }
                myConnection.Close();

            }
            catch (Exception ee) { Console.WriteLine("Failed: InsertCities"); Console.ReadKey(); }
        }
        /*
         * следующие 5 методов создают всю базу даннх
         */
        public void createCity(String Connect)
        {
            string createCity = "create table city (id_city INT NOT NULL , city VARCHAR(20) NOT NULL , IATA VARCHAR(4) NOT NULL ,country_code VARCHAR(4) NOT NULL ,PRIMARY KEY (id_city) );";
            //string Connect = "Database=meta;Data Source=localhost;User Id=root;Password=system;";
            MySqlConnection myConnection = new MySqlConnection(Connect);
            //try
            //{
            myConnection.Open();
            MySqlCommand City = new MySqlCommand(createCity, myConnection);
            City.ExecuteNonQuery();
            Console.WriteLine("Done: City");

            myConnection.Close();
            //}
            //catch (Exception ee) { Console.WriteLine("Failed: City"); Console.ReadKey(); }
        }
        public void createUser(String Connect)
        {
            string createUser = "create table user (id_user INT NOT NULL , login VARCHAR(15) NOT NULL ,password VARCHAR(15) NOT NULL ,access_lvl VARCHAR(5) NOT NULL , PRIMARY KEY (id_user) );";
            //string Connect = "Database=meta;Data Source=localhost;User Id=root;Password=system;";
            MySqlConnection myConnection = new MySqlConnection(Connect);
            try
            {
                myConnection.Open();
                MySqlCommand Users = new MySqlCommand(createUser, myConnection);
                Users.ExecuteNonQuery();
                Console.WriteLine("Done: Users");

                myConnection.Close();
            }
            catch (Exception ee) { Console.WriteLine("Failed: User"); Console.ReadKey(); }
        }
        public void createRequest(String Connect)
        {
            string createRequest = "create table Request (id_request INT NOT NULL , resource VARCHAR(30) NOT NULL , status INT NOT NULL , day_start DATE NOT NULL , day_finish DATE NOT NULL , frequency INT NOT NULL , city_from VARCHAR(100) NOT NULL , city_to VARCHAR(100) NOT NULL, statistic VARCHAR(30) NOT NULL, current_day DATE NOT NULL , PRIMARY KEY (id_request) );";
            //string Connect = "Database=meta;Data Source=localhost;User Id=root;Password=system;";
            MySqlConnection myConnection = new MySqlConnection(Connect);
            try
            {
                myConnection.Open();
                MySqlCommand RUni = new MySqlCommand(createRequest, myConnection);

                RUni.ExecuteNonQuery();
                Console.WriteLine("Done: Request");

                myConnection.Close();
            }
            catch (Exception ee) { Console.WriteLine("Failed: Request"); Console.ReadKey(); }
        }
        public void createQuery(String Connect)
        {
            string createQuery = "create table Query (id_query INT NOT NULL,date_browse DATE NOT NULL, date_query DATE NOT NULL, city_from VARCHAR(100) NOT NULL ,city_to VARCHAR(100) NOT NULL, resource VARCHAR(30) NOT NULL, PRIMARY KEY (id_query));";
            //CONSTRAINT id_user_inc FOREIGN KEY (id_user) REFERENCES user(id_user) ON DELETE NO ACTION ,PRIMARY KEY (id_request)
            //string Connect = "Database=meta;Data Source=localhost;User Id=root;Password=system;";
            MySqlConnection myConnection = new MySqlConnection(Connect);
            try
            {
                myConnection.Open();
                MySqlCommand RInc = new MySqlCommand(createQuery, myConnection);
                RInc.ExecuteNonQuery();
                Console.WriteLine("Done: Query");
                myConnection.Close();
            }
            catch (Exception ee) { Console.WriteLine("Failed: Query"); Console.ReadKey(); }
        }
        public void createRequest_Query(String Connect)
        {
            string createRec_Que = "create table Request_Query (id_request INT NOT NULL,id_query INT NOT NULL, index(id_request), constraint id_req foreign key (id_request) references request(id_request) ON DELETE NO ACTION, primary key (id_query, id_request), index(id_query), constraint id_que foreign key (id_query) references query(id_query) ON DELETE NO ACTION );";
            MySqlConnection myConnection = new MySqlConnection(Connect);
            try
            {
                myConnection.Open();
                MySqlCommand RQ = new MySqlCommand(createRec_Que, myConnection);
                RQ.ExecuteNonQuery();
                Console.WriteLine("Done: Request_Query");
                myConnection.Close();
            }
            catch (Exception ee) { Console.WriteLine("Failed: Request_Query"); Console.ReadKey(); }
        }
        public void createMain(String Connect)
        {
            string createMain = "create table Main ( id_field INT NOT NULL , resource VARCHAR(30) NOT NULL , price FLOAT NOT NULL , city_from VARCHAR(100) NOT NULL , city_to VARCHAR(100) NOT NULL ,date_dep DATE NOT NULL , date_ret DATE NOT NULL, trans_company VARCHAR(30) NOT NULL , class VARCHAR(4) NOT NULL, date_collect DATE NOT NULL, PRIMARY KEY (id_field) );";


            MySqlConnection myConnection = new MySqlConnection(Connect);
            try
            {
                myConnection.Open();
                MySqlCommand cMain = new MySqlCommand(createMain, myConnection);
                cMain.ExecuteNonQuery();
                Console.WriteLine("Done: Main");

                myConnection.Close();
            }
            catch (Exception ee) { Console.WriteLine("Failed: Main"); Console.ReadKey(); }
        }
        /*
         * Возвращает количество записей в выбранной таблице
         */
        public int getRecordNumber(String Connect, String table_name, String selectOption)
        {

            string getRecNum = "select count(*) from " + table_name + " " + selectOption + ";";
            int record_count = 0;

            MySqlConnection myConnection = new MySqlConnection(Connect);
            MySqlCommand getRec = new MySqlCommand(getRecNum, myConnection);
            try
            {

                myConnection.Open();

                MySqlDataReader mreader = getRec.ExecuteReader();
                mreader.Read();
                record_count = mreader.GetInt32(0);
                mreader.Close();
                myConnection.Close();

            }
            catch (Exception ee) { Console.WriteLine("Faile: GetRecordNumber "+ee.ToString()); Console.ReadKey(); }
            return record_count;
        }
        /*
         * Формирует таблицу Query с помощью записей в Request со статусом 0
         */
        public void formQueryByRequest(String Connect, int requestId, String mode)
        {
            int id_request = 0;
            String resource = "";
            int status;
            DateTime day_start;
            DateTime day_finish;
            int frequency = 0;
            String city_from = "";
            String city_to = "";
            DateTime current_date;
            string statistic = "";
            int RecNumQuery = 0;
            MySqlConnection metaConnection = new MySqlConnection(Connect);
            String checkStat = "select status from request where id_request=" + requestId;
            MySqlCommand checkStatus = new MySqlCommand(checkStat, metaConnection);
            metaConnection.Open();
            MySqlDataReader statusReader = checkStatus.ExecuteReader();
            statusReader.Read();
            int req_status = statusReader.GetInt32(0);
            metaConnection.Close();
            if (req_status == 0)
            {
                String SelFromReq = "select * from request where id_request=" + requestId;
                MySqlCommand SelectFromRequest = new MySqlCommand(SelFromReq, metaConnection);
                //String getRecNumQue = "select count(*) from query";
                //MySqlCommand getRecordNumberQuery = new MySqlCommand(getRecNumQue, metaConnection);
                metaConnection.Open();
                MySqlDataReader RequestContains = SelectFromRequest.ExecuteReader();
                RequestContains.Read();
                id_request = requestId;
                resource = RequestContains.GetString(1);
                status = RequestContains.GetInt32(2);
                day_start = RequestContains.GetDateTime(3);
                day_finish = RequestContains.GetDateTime(4);
                frequency = RequestContains.GetInt32(5);
                city_from = RequestContains.GetString(6);
                city_to = RequestContains.GetString(7);
                statistic = RequestContains.GetString(8);
                current_date = RequestContains.GetDateTime(9);
                RequestContains.Close();
                //RequestContains = getRecordNumberQuery.ExecuteReader();
                //RequestContains.Read();
                //RecNumQuery = RequestContains.GetInt32(0);
                //RequestContains.Close();
                QueryPattern[] que = this.selectFromQuery(Connect, "");
                int pos=0;
                if (que.Length > 0)
                {
                    for (int j = 0; j < que.Length; j++)
                        pos = que[j].id_query;
                }
                RequestPattern requestMain = new RequestPattern(id_request, resource, status, day_start, day_finish, frequency, city_from, city_to, statistic, current_date);
                int dayS = day_start.Day;
                int monthS = day_start.Month;
                int yearS = day_start.Year;
                int dayF = day_finish.Day;
                int monthF = day_finish.Month;
                int yearF = day_finish.Year;

                int daysToInspect = 0;
                if (yearF == yearS)
                {
                    if (monthS == monthF)
                    {
                        daysToInspect = dayF - dayS;
                        if (daysToInspect < 0) daysToInspect = -1;
                    }
                    else if (monthS > monthF)
                        daysToInspect = -1;
                    else
                    {
                        int daysThisMonth = this.days_per_month[monthS] - dayS;
                        int count = monthS + 1;
                        int tmp = 0;
                        while (count < monthF)
                        {
                            tmp += days_per_month[count];
                            count++;
                        }
                        daysToInspect = daysThisMonth + tmp + dayF;
                    }
                }
                else if (yearS > yearF)
                    daysToInspect = -1;
                else
                {
                    int year_diff = (yearF - yearS) * 365;
                    for (int j = 1; j <= monthS; j++)
                        year_diff = year_diff - days_per_month[j];
                    year_diff = year_diff - dayS;
                    for (int k = 1; k <= monthF; k++)
                        daysToInspect += days_per_month[k];
                    daysToInspect += dayF + year_diff;
                }
                int daysCount = 0;
                //int idPlace = ++RecNumQuery;
                if (daysToInspect == -1)
                    Console.WriteLine("                      Failed : Forming request : Invalid date.");
                else
                {
                    int idPlace = ++pos;
                    if (requestMain.statistic.Equals("4type") == true)
                    {
                        while (daysCount <= daysToInspect)
                        {

                            int len_from = requestMain.city_from.Length;
                            int len_to = requestMain.city_to.Length;
                            int countdut1 = 0;
                            int countdut2 = 0;
                            while (countdut1 < len_from)
                            {
                                while (countdut2 < len_to)
                                {
                                    if (requestMain.city_from.Substring(countdut1, 3) == requestMain.city_to.Substring(countdut2, 3))
                                    {
                                        Console.WriteLine("                      " + requestMain.city_to.Substring(countdut2, 3)+"-"+requestMain.city_to.Substring(countdut2, 3) + " denied.");
                                        countdut2 += 3;
                                    }
                                    else
                                    {
                                        String insertCommand = "insert into query (id_query, date_browse, date_query, city_from, city_to, resource) values (" + idPlace + ", \"" + day_finish.Year + "-" + day_finish.Month + "-" + day_finish.Day + "\", \"" + day_start.Year + "-" + day_start.Month + "-" + day_start.Day + "\", \"" + requestMain.city_from.Substring(countdut1, 3) + "\", \"" + requestMain.city_to.Substring(countdut2, 3) + "\", \"" + requestMain.resource + "\");";
                                        MySqlCommand insQue = new MySqlCommand(insertCommand, metaConnection);
                                        insQue.ExecuteNonQuery();

                                        String Request_Query = "insert into request_query (id_request, id_query) values (" + requestId + ", " + idPlace + ");";
                                        MySqlCommand insertReq_Que = new MySqlCommand(Request_Query, metaConnection);
                                        insertReq_Que.ExecuteNonQuery();
                                        idPlace++;
                                        countdut2 += 3;
                                    }
                                }
                                countdut2 = 0;
                                countdut1 += 3;
                            }
                            day_start = day_start.AddDays(requestMain.frequency);
                            daysCount += requestMain.frequency;
                        }
                    }
                    if (requestMain.statistic.Equals("1type") == true || requestMain.statistic.Equals("2type") == true || requestMain.statistic.Equals("3type") == true)
                    {
                        while (daysCount <= daysToInspect)
                        {

                            int len_from = requestMain.city_from.Length;
                            int len_to = requestMain.city_to.Length;
                            int countdut1 = 0;
                            int countdut2 = 0;
                            while (countdut1 < len_from)
                            {
                                while (countdut2 < len_to)
                                {
                                    if (requestMain.city_from.Substring(countdut1, 3) == requestMain.city_to.Substring(countdut2, 3))
                                    {
                                        Console.WriteLine("                      " + requestMain.city_to.Substring(countdut2, 3) + "-" + requestMain.city_to.Substring(countdut2, 3) + " denied.");
                                        countdut2 += 3;
                                    }
                                    else
                                    {
                                        String insertCommand = "insert into query (id_query, date_browse, date_query, city_from, city_to, resource) values (" + idPlace + ", \"" + day_start.Year + "-" + day_start.Month + "-" + day_start.Day + "\", \"" + day_start.Year + "-" + day_start.Month + "-" + day_start.Day + "\", \"" + requestMain.city_from.Substring(countdut1, 3) + "\", \"" + requestMain.city_to.Substring(countdut2, 3) + "\", \"" + requestMain.resource + "\");";
                                        MySqlCommand insQue = new MySqlCommand(insertCommand, metaConnection);
                                        insQue.ExecuteNonQuery();

                                        String Request_Query = "insert into request_query (id_request, id_query) values (" + requestId + ", " + idPlace + ");";
                                        MySqlCommand insertReq_Que = new MySqlCommand(Request_Query, metaConnection);
                                        insertReq_Que.ExecuteNonQuery();
                                        idPlace++;
                                        countdut2 += 3;
                                    }
                                }
                                countdut2 = 0;
                                countdut1 += 3;
                            }
                            day_start = day_start.AddDays(requestMain.frequency);
                            daysCount += requestMain.frequency;
                        }
                    }
                }
                //tut
                String update = "update request set status=1 where id_request=" + requestId;
                MySqlCommand updateRequest = new MySqlCommand(update, metaConnection);
                updateRequest.ExecuteNonQuery();
                metaConnection.Close();

            }
            //nen
        }
        /*
         * Возвращает массив объектов RequestPattern из таблицы Request
         */
        public RequestPattern[] selectFromRequest(String Connect, String searchOption) //searchOption является опцией типа "where id_request = 1", если опция не требуется - задавать "". 
        {                                                                              //параметры типа String или DateTime в аргументе searchOption задаются следующим "where resource = \"ozon\"".
            MySqlConnection metaConnection = new MySqlConnection(Connect);
            String selectR = "select * from request " + searchOption + ";";
            MySqlCommand selectRequest = new MySqlCommand(selectR, metaConnection);
            RequestPattern[] Requests = new RequestPattern[this.getRecordNumber(Connect, "request", searchOption) ];
            if (Requests.Length > 0)
            {
                try
                {
                    metaConnection.Open();
                    MySqlDataReader selectReader = selectRequest.ExecuteReader();
                    int i = 0;
                    while (selectReader.Read())
                    {
                        Requests[i] = new RequestPattern(selectReader.GetInt32(0), selectReader.GetString(1), selectReader.GetInt32(2), selectReader.GetDateTime(3), selectReader.GetDateTime(4), selectReader.GetInt32(5), selectReader.GetString(6), selectReader.GetString(7), selectReader.GetString(8), selectReader.GetDateTime(9));

                        i++;
                    }
                    selectReader.Close();
                    metaConnection.Close();
                }
                catch (Exception ee) { Console.WriteLine("Failed: selectFromRequest "+ee.ToString()); Console.ReadKey(); }
            }
            return Requests;
        }
        /*
         * Возвращает массив объектов QueryPattern из таблицы Query
         */
        public QueryPattern[] selectFromQuery(String Connect, String searchOption) //searchOption является опцией типа "where id_query = 1", если опция не требуется - задавать "". 
        {                                                                              //параметры типа String или DateTime в аргументе searchOption задаются следующим "where resource = \"ozon\"".
            MySqlConnection metaConnection = new MySqlConnection(Connect);
            String selectq = "select * from query " + searchOption + ";";
            //Console.WriteLine(selectq);
            MySqlCommand selectQuery = new MySqlCommand(selectq, metaConnection);
            QueryPattern[] Queries = new QueryPattern[this.getRecordNumber(Connect, "query", searchOption)];
            if (Queries.Length > 0)
            {
                try
                {
                    metaConnection.Open();
                    MySqlDataReader selectReader = selectQuery.ExecuteReader();
                    int i = 0;
                    while (selectReader.Read())
                    {
                        Queries[i] = new QueryPattern(selectReader.GetInt32(0), selectReader.GetDateTime(1), selectReader.GetDateTime(2), selectReader.GetString(3), selectReader.GetString(4), selectReader.GetString(5));

                        i++;
                    }
                    selectReader.Close();
                    metaConnection.Close();
                }
                catch (Exception ee) { Console.WriteLine("Failed: selectFromRequest"); Console.ReadKey(); }
            }
            return Queries;
        }
        /*
         * Вставляет запись в таблицу Main
         */
        public void insertIntoMain(String Connect, String resource, String city_from, String city_to, DateTime date_dep, DateTime date_ret, int price, String trans_company, String clas, DateTime date_collect)
        {
            MySqlConnection metaConnection = new MySqlConnection(Connect);
            int recNum = this.getRecordNumber(Connect, "main", "");
            recNum += 10;
            DateTime gh = date_collect;
            gh = DateTime.Today;
            //Console.WriteLine(gh);
            String insertM = "insert into main (id_field, resource, price, city_from, city_to, date_dep, date_ret, trans_company, class, date_collect) values (" + recNum + ", \"" + resource + "\", " + price + ", \"" + city_from + "\", \"" + city_to + "\", \"" + date_dep.Year + "-" + date_dep.Month + "-" + date_dep.Day + " " + date_dep.TimeOfDay + "\", \"" + date_ret.Year + "-" + date_ret.Month + "-" + date_ret.Day + " " + date_ret.TimeOfDay + "\", \"" + trans_company + "\", \"" + clas + "\", \"" + gh.Year + "-" + gh.Month + "-" + gh.Day + " " + gh.TimeOfDay + "\");";
            MySqlCommand insertMain = new MySqlCommand(insertM, metaConnection);
            try
            {
                metaConnection.Open();
                insertMain.ExecuteNonQuery();
                //Console.WriteLine("Done: insertIntoMain");
                metaConnection.Close();
            }
            catch (Exception ee) { Console.WriteLine("Failed: insertIntoMain"+ee.ToString());}
        }
        /*
         * selectFronQuery на мотив OneTwoTrip
         */
        public Order[] getQueryOneTwo()
        {
            DateTime cur = DateTime.Today;
            string curdate = cur.ToString();
            //string Connect = "Database=meta;Data Source=localhost;User Id=root;Password=assa";
            string Connect = "Database=meta;Data Source=188.35.9.179;User Id=assa;Password=assa";
            string getQuery = "select * from Query where date_query = " + "'" + cur.ToString("yyyy-MM-dd") + "'" + "and resource=\"12travel\"";
            string getRecNum = "select count(*) from Query where date_query = " + "'" + cur.ToString("yyyy-MM-dd") + "'" + "and resource=\"12travel\"";
            int id_query = 0;
            string date_browse = "";
            string date_query = "";
            string city_from = "";
            string city_to = "";
            string resource = "";

            MySqlConnection myConnection = new MySqlConnection(Connect);
            MySqlCommand getQue = new MySqlCommand(getQuery, myConnection);
            MySqlCommand getRec = new MySqlCommand(getRecNum, myConnection);
            try
            {
                myConnection.Open();
                //Подсчет строк
                MySqlDataReader countreader = getRec.ExecuteReader();
                countreader.Read();
                int cou = countreader.GetInt32(0);
                countreader.Close();

                MySqlDataReader mreader = getQue.ExecuteReader();
                Order[] orders = new Order[cou];
                int i = 0;
                while (mreader.Read())
                {
                    id_query = mreader.GetInt32(0);
                    date_browse = mreader.GetString(1);
                    date_query = mreader.GetString(2);
                    city_from = mreader.GetString(3);
                    city_to = mreader.GetString(4);
                    resource = mreader.GetString(5);

                    //Заполнение массива заявок для последующей отправки запросов
                    int
                    order_id = id_query;
                    string
                    source = resource,
                    date = date_browse,
                    from = city_from,
                    to = city_to;
                    orders[i] = new Order(order_id, source, date, from, to);
                    i++;

                }
                mreader.Close();
                myConnection.Close();
                return orders;
            }
            catch (Exception ee) { Console.WriteLine("damn!!!!!"); Console.ReadKey(); return null; }
    
        }
        /*
         * Удаляет отработанные запросы 
         */
        public void deleteFromQuery(String Connect, int id_query)
        {
            String deleteQ = "delete from query where id_query=" + id_query;
            String deleteRQ = "delete from request_query where id_query="+id_query;
            MySqlConnection meta = new MySqlConnection(Connect);
            MySqlCommand deleteQue = new MySqlCommand(deleteQ, meta);
            MySqlCommand deleteReqQue = new MySqlCommand(deleteRQ, meta);
            try
            {
                meta.Open();
                deleteReqQue.ExecuteNonQuery();
                deleteQue.ExecuteNonQuery();
                meta.Close();
            }
            catch (Exception ee) { Console.WriteLine("Failed: deleteFromQuery"); }
        }
        /*
         * Проверяет заявки на предмет их отработки. Отработанные помечает статусом 2
         */
        public void checkStatus_setStatus2(String Connect)
        {
            int recNumReq_Que;
            String update_query="";
            MySqlConnection metaConnection = new MySqlConnection(Connect);
            RequestPattern[] reqStat1 = this.selectFromRequest(Connect, "where status=1");
            for (int i = 0; i < reqStat1.Length; i++)
            {
                recNumReq_Que = this.getRecordNumber(Connect, "request_query", "where id_request="+reqStat1[i].id_request);
                if (recNumReq_Que == 0)
                {
                    update_query = "update request set status=2 where id_request=" + reqStat1[i].id_request + ";";
                    MySqlCommand update_que = new MySqlCommand(update_query, metaConnection);
                    try
                    {
                        metaConnection.Open();
                        update_que.ExecuteNonQuery();
                        metaConnection.Close();
                    }
                    catch (Exception ee) { Console.WriteLine("Failed:checkStatus_setStatus2decwevwerg "+ee.ToString()); Console.ReadKey(); }
                }  
            }
        }
        /*
         * Возвращает массив пользователей из таблицы request_user
         */
        public int[] getUsers(String Connect)
        {
            int[] users= new int[this.getRecordNumber(Connect, "request_user", "")];
            MySqlConnection metaConnection = new MySqlConnection(Connect);
            String select_u = "select id_user from request_user;";
            MySqlCommand select_users = new MySqlCommand(select_u, metaConnection);
            MySqlDataReader select_from_users;
            int i = 0;
            try
            {
                metaConnection.Open();
                select_from_users = select_users.ExecuteReader();
                while (select_from_users.Read())
                {
                    users[i] = select_from_users.GetInt32(0);
                    
                    i++;
                }
            }
            catch (Exception ee) { Console.WriteLine("Failed:getUsers: "+ee.ToString());  Console.ReadKey(); }
            return users;
        }
        
    }
}
